
-- Recommend max degree of parallelism = 0
-- Recommend tempdb split into x files of equal size, where x = #processors (cores)
-- Recommend split weblogs into x files of equal size where x = #processors (cores)
-- Recommend disabling autogrow on weblogs data files and sizing them to accomodate the data being imported

USE [master]

if not exists (select * from sys.databases where name = 'weblogs')
Begin
	CREATE DATABASE [WebLogs];
	ALTER DATABASE [WebLogs] set RECOVERY SIMPLE;
	ALTER DATABASE [WebLogs] MODIFY FILE ( NAME = N'WebLogs', FILEGROWTH = 100MB );

End
go

use [WebLogs] 
if schema_id('iislogging') is null 
	exec ('create schema [iislogging] authorization [DBO];')

IF  EXISTS (SELECT * from information_schema.ROUTINES where SPECIFIC_SCHEMA = 'iislogging' and SPECIFIC_NAME = 'fnWebGetColNames')
    DROP FUNCTION [iislogging].[FnWebGetColNames]
GO
 
CREATE FUNCTION [iislogging].[fnWebGetColNames] (@IN_string NVARCHAR(512))
RETURNS @Results TABLE (RowNumb int, COLName NVARCHAR(128))
AS
-- This function accepts a SPACE seperated string and returns a table of the individual values
-- The string should look like 'a,b,c,d,e,f,g' with no leading or trailing delimiters.  
-- It uses a recursive CTE and CHARINDEX to parse the text string.
BEGIN  
	--Test for problems and add the delimeter to the end of the string if it's not there already
	DECLARE @delimiter CHAR(1) = ' ' 
    IF  (LEN(@IN_string) < 1 OR @IN_string IS NULL) RETURN;
	ELSE IF (RIGHT(@IN_string,1) <> @delimiter) SET @IN_string=@IN_string+@delimiter ;
	-- the CTE returns the starting and ending locations of the values within the string
	WITH CTEx(Starting,Ending)
    AS  
	(
		--Recursive CTE requires a top level UNION ALL...so the first row is static
		SELECT 1, CAST(CHARINDEX(@delimiter,@IN_string) as smallint)
		UNION ALL 
		-- The rest of the rows are built recursively
		SELECT Ending+1, CAST(CHARINDEX(@delimiter,@IN_string,Ending+1) as smallint)
		FROM CTEx 
		WHERE CHARINDEX(@delimiter,@IN_string,Ending+1)>0
	)
	--Finally, use the starting and ending positions to SUBSTRING the text.  Return the row-number() to keep it in order.     
    INSERT  INTO @Results  (RowNumb,COLName)
    SELECT  Row_Number() OVER (ORDER BY Starting), SUBSTRING(@IN_string,Starting,Ending-Starting)
    FROM    CTEx  
    RETURN
END   
GO

if  object_id(N'iislogging.logfiles',N'U') is not null 
BEGIN
	ALTER TABLE [iislogging].[WebLog] DROP CONSTRAINT [FK_WebLog_LogFiles]
	ALTER TABLE [iislogging].[IISResets] DROP CONSTRAINT [FK_IISResets_LogFiles]
	DROP TABLE [iislogging].[LogFiles]
END 
go
CREATE TABLE [iislogging].[LogFiles](
	[LogFile_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[LogFileName] [nchar](255) NOT NULL,
	[ImportDate] [datetime] NOT NULL, 
	[Completed] [Binary]
 	CONSTRAINT [PK_LogFiles] PRIMARY KEY nonCLUSTERED ([LogFile_ID]) WITH (data_compression=PAGE))
CREATE CLUSTERED INDEX [LogFiles_Index_ImportDate] ON [iislogging].[logfiles] ([importDate]) 
go
if  object_id(N'iislogging.IISResets',N'U') is not null 
begin
	DROP TABLE [iislogging].[IISResets]
end
CREATE TABLE [iislogging].[IISResets](
	[IISReset_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[LogFile_ID] [bigint] NOT NULL,
	[ResetDate] [datetime] NOT NULL,
	CONSTRAINT [PK_IISResets] PRIMARY KEY nonCLUSTERED ([IISReset_ID]) WITH (data_compression=PAGE)) 
ALTER TABLE [iislogging].[IISResets]  WITH CHECK ADD  CONSTRAINT [FK_IISResets_LogFiles] FOREIGN KEY([LogFile_ID])
REFERENCES [iislogging].[LogFiles] ([LogFile_ID])
CREATE CLUSTERED INDEX [IISResets_Index_ResetDate] ON [iislogging].[iisresets] ([ResetDate]) 
go
if  object_id(N'iislogging.Weblog',N'U') is not null 
begin
	drop table [IISLogging].[WebLog] 
end

CREATE TABLE [iislogging].[WebLog](
	[WebLog_ID] [bigint] IDENTITY NOT NULL,
	[LogFile_ID] [bigint] NOT NULL,
	[Date] [date] NULL,
	[Time] [time](0) NULL,
	[s-sitename] [nvarchar](255) NULL,
	[s-computername] [nvarchar](255) NULL,
	[s-ip] [nvarchar](37) NULL,
	[cs-Method] [nvarchar](3) NULL,
	[cs-uri-stem] [nvarchar](255) NULL,
	[cs-uri-query] [nvarchar](2048) NULL,
	[s-port] [int] NULL,
	[cs-username] [nvarchar](255) NULL,
	[c-IP] [nvarchar](37) NULL,
	[cs-version] [nvarchar](50) NULL,
	[cs(user-agent)] [nvarchar](512) NULL,
	[cs(cookie)] [varchar](5120) NULL,
	[cs(referer)] [nvarchar](2048) NULL,
	[cs-host] [nvarchar](255) NULL,
	[sc-status] [smallint] NULL,
	[sc-substatus] [bigint] NULL,
	[sc-win32-status] [bigint] NULL,
	[sc-bytes] [bigint] NULL,
	[cs-bytes] [bigint] NULL,
	[time-taken] [bigint] NULL,
	PRIMARY KEY NONCLUSTERED ([WebLog_ID]) with (data_compression=PAGE) ) 

CREATE CLUSTERED INDEX [WebLog_Index_DateTime] ON [iislogging].[WebLog] ([Date] ASC,[Time] ASC) ON [PRIMARY]
/****** Object:  Index [Weblog_logfile_ID]    Script Date: 9/20/2013 3:39:45 PM ******/
CREATE NONCLUSTERED INDEX [Weblog_logfile_ID] ON [iislogging].[WebLog]
([LogFile_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [iislogging].[WebLog]  WITH CHECK ADD  CONSTRAINT [FK_WebLog_LogFiles] FOREIGN KEY([LogFile_ID])
REFERENCES [iislogging].[LogFiles] ([LogFile_ID])
ALTER TABLE [iislogging].[WebLog] CHECK CONSTRAINT [FK_WebLog_LogFiles]

if  object_id(N'iislogging.vw_WebLog_Friendly_Names',N'V') is not null 
	drop View [IISLogging].[vw_WebLog_Friendly_Names] 
go 
CREATE VIEW [iislogging].[vw_WebLog_Friendly_Names]
AS
SELECT	WebLog_ID, 
		Date, 
		Time, 
		[s-sitename] AS SiteName, 
		[s-computername] AS ComputerName, 
		[s-ip] AS ServerIPAddress, 
		[cs-Method] AS Method, 
		[cs-uri-stem] AS URIStem, 
        [cs-uri-query] AS URIQuery, 
		[s-port] AS ServerPort, 
		[cs-username] AS UserName, 
		[c-IP] AS ClientIPAddress, 
		[cs-version] AS ProtocolVersion, 
        [cs(user-agent)] AS UserAgent, 
		[cs(cookie)] AS Cookie, 
		[cs(referer)] AS Referer, 
		[cs-host] AS Host, 
		[sc-status] AS HTTPStatus, 
        [sc-substatus] AS ProtocolSubStatus, 
		[sc-win32-status] AS Win32Status, 
		[sc-bytes] AS BytesSent, 
		[cs-bytes] AS BytesReceived, 
		[time-taken] AS TimeTaken
FROM     iislogging.WebLog


